<?php

function exitScript() {
	echo("<tr><td colspan=\"4\">Fehler bei Ajax-Kommunikation.</td></tr>");
	exit(1);
}

if((!isset($_GET["mode"])) || empty($_GET["mode"]))
exitScript();

include 'dbaccess.php';

//$dbhost="neptun.fbe.fh-weingarten.de:3306";
$dbhost="127.0.0.1:13306";
$dbuser="webprog_01";
$dbpass="webprog_01";
$usedb="webprog_01";


$tDbQueryer = new DbAccess();

try {
	$tDbQueryer->connect($dbhost, $dbuser, $dbpass, $usedb);
} catch (Exception $e) {
	echo($e->getMessage());
	exit(-1);
}


$ResultString="";

switch($_GET["mode"]) {
	case "init":

		if(!isset($_GET["part"]) || empty($_GET["part"]))
			exitScript();
			
		$query="";

		switch($_GET["part"]) {
			case "spselect":
				$query="SELECT Name FROM Studienschwerpunkte ORDER BY Name";
				break;
			case "stortselect":
				$query="SELECT Standort FROM Firmen ORDER BY Standort";
				break;
			case "subjselect":
				$query="SELECT Name FROM Themen ORDER BY Name";
				break;
			default: break;
		}

		try {
			$tDbQueryer->pushQuery($query);
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}

		$optCont="";
		
		while ($tDbQueryer->hasMoreResults()) {
			$line = $tDbQueryer->nextResultLine();

			switch($_GET["part"]) {
				case "spselect":
					$ResultString.=sprintf("<option>%s</option>\n",$line["Name"]);
					break;
				case "stortselect":
					$ResultString.=sprintf("<option>%s</option>\n",$line["Standort"]);
					break;
				case "subjselect":
					$ResultString.=sprintf("<option>%s</option>\n",$line["Name"]);
					break;
				default: break;
			}
		}
		break;
	case "compinfo":

		if(!isset($_GET["company"]) || empty($_GET["company"]))
			exitScript();
						
		$line="";
		
		try {
			$tDbQueryer->doQuery("SELECT Name, fid, bew_cnt, bew_avg, Straße, PLZ, Standort, URL, Email FROM Firmen WHERE name='".$_GET["company"]."'");
			$line = $tDbQueryer->nextResultLine();
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}
		
		$fid=$line['fid'];
		
		$ResultString.="<h1 id=\"detFirmaName\">".$line['Name']."</h1>\n";		
		
		$ResultString.="<table>\n<thead>\n<tr class=\"text_center\">\n
						<th>Anschrift</th>\n<th>Kontakt</th>\n
						</tr>\n</thead>\n<tbody><tr>\n";
		
		$ResultString.=sprintf("<td class=\"td_detFirma\" >%s<br />%s %s</td>\n",$line["Straße"],$line["PLZ"],$line["Standort"]);
		$ResultString.=sprintf("<td class=\"td_detFirma\" >eMail: %s<br />Web: %s</td>\n",$line["Email"],$line["URL"]);
		$ResultString.="</tr>\n</tbody>\n</table>\n";
		
		$ResultString.=sprintf("<h3>Schnitt: %s - Bewertungen: %s</h3>\n",$line["bew_avg"],$line["bew_cnt"]);
		
		try {
			$tDbQueryer->doQuery("SELECT Th.Name FROM Themen Th, Behandelt_Thema BeTh WHERE BeTh.FID_FK=$fid AND Th.TID = BeTh.TID_FK");
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}
		
		$ResultString.="<table><tr>\n<td>\n<b>Themengebiete: </b>\n</td>\n<td>\n";
		
		$i=0;
		
		while ($tDbQueryer->hasMoreResults()) {
			$line = $tDbQueryer->nextResultLine();
			$ResultString.=$line['Name'];

			if($i==2) {
				$i=0;
				$ResultString.="<br/>";
			}
			else{
				$i++;
				$ResultString.=" ";
			}
		}

		$ResultString.="</td>\n</tr>\n</table>\n";
		
		try {
			$tDbQueryer->doQuery("SELECT Bewertung, Kommentar FROM Bewertungen WHERE Gehoert_Zu_FID=$fid");
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}

		$even=0;
		$textKomCount=0;
		$ResultString.="<div id=\"bewertungen\">";


		while ($tDbQueryer->hasMoreResults()) {
			$line = $tDbQueryer->nextResultLine();
				
			if($line['Kommentar']=="")
			continue;
				
			$ResultString.="<div class=\"comment ";
			$ResultString.=($even % 2 == 0)?"lsilver\">":"llsilver\">";
			$ResultString.=sprintf("\nBewertung %s<br />Kommentar %s\n</div>\n",$line['Bewertung'],$line['Kommentar']);

			$even++;
			$textKomCount++;
		}

		if($textKomCount==0)
			$ResultString.="<b>Bisher keine Bewertung mit Kommentar.</b>";
		
		$ResultString.="</div>\n<table>\n<tr>\n<td class=\"top right\">Bewertung:</td>\n";
		$ResultString.="<td><select id=\"neueBew\">\n";
			
		for($i=1;$i<6;$i++)
		$ResultString.=sprintf("<option>%s</option>\n",$i);
			
		$ResultString.="</select>\n(1=schlecht / 5=sehr gut)</td>\n</tr>\n";
		$ResultString.="<tr>\n<td class=\"top right\">Kommentar:</td>\n";
		$ResultString.="<td><textarea id=\"neuerKom\" cols=\"40\" rows=\"5\" /></td>\n</tr>\n";
		$ResultString.="<tr>\n<td></td>\n<td>\n<input type=\"button\" onclick=\"onWriteComment()\" value=\"Absenden\" />\n</td>\n</tr>\n";
		$ResultString.="</table>\n";
		break;

	case "chgfilter":

		$sp=$so=$bw=$th=null;
		$selectedFilds="F.FID AS FID, F.Name, F.Standort, F.bew_cnt, F.bew_avg";
		$selectedTables="Firmen F";
		$spSubQuery="";
		$thSubQuery="";
		$soList="";
		$bw=$_GET['bw'];

		if(isset($_GET['sp'])) {
			$spList="";
			$spSubQuery="(SELECT * FROM Studienschwerpunkte Sp, DecktAb_Schwerpunkt DASp WHERE DASp.FID_FK=FID AND Sp.SID=DASp.SID_FK AND ";
			
			foreach ($_GET['sp'] as $tmp)
				$spList .= "Sp.Name = '$tmp' OR ";
			
			$spList = substr($spList, 0, -4);
			$spSubQuery.="($spList))";	
		}
		
		
		if(isset($_GET['th'])) {
			$thList="";
			$thSubQuery="(SELECT * FROM Themen Th, Behandelt_Thema BeTh WHERE BeTh.FID_FK=FID AND BeTh.TID_FK = Th.TID AND ";
				
			foreach ($_GET['th'] as $tmp)
				$thList .= "Th.Name = '$tmp' OR ";
				
			$thList = substr($thList, 0, -4);
			$thSubQuery.="($thList))";			
		}
		
		
		if(isset($_GET['so'])) {			
			foreach ($_GET['so'] as $tmp)
				$soList .= "F.Standort = '$tmp' OR ";
				
			$soList = substr($soList, 0, -4);
			$soList = "($soList)";
		}	

		
		$query = sprintf("SELECT %s FROM %s WHERE ",$selectedFilds,$selectedTables);

		$query.="bew_avg <= $bw ";
		
		if(!empty($soList) || !isset($bw))
			$query.="AND $soList ";
		
		if(!empty($spSubQuery))
			$query.="AND EXISTS $spSubQuery ";
			
		if(!empty($thSubQuery)) {
			if(!empty($spSubQuery) || !empty($soList) || isset($bw))
				$query.="AND ";
			
			$query.="EXISTS $thSubQuery ";	
		}

		
		try {
			$tDbQueryer->pushQuery($query);
		} catch (Exception $e) {
			$ResultString.=$query;
			$ResultString.=$e->getMessage();
			break;
		}
			
		while ($tDbQueryer->hasMoreResults()) {
			$line = $tDbQueryer->nextResultLine();
				
			$fid=$line['FID'];

			try {
				$tDbQueryer->pushQuery("SELECT Name FROM Studienschwerpunkte, DecktAb_Schwerpunkt WHERE FID_FK = $fid AND SID = SID_FK");
			} catch (Exception $e) {
				$ResultString.=$e->getMessage();
				break;
			}
				
			$ResultString.="<tr>\n<td class=\"clickable border_right\">".$line['Name']."</td>\n";
			$ResultString.="<td class=\"border_right\">";

			while ($tDbQueryer->hasMoreResults()) {
				$spname = $tDbQueryer->nextResultLine();
				$ResultString.=sprintf("%s<br/>",$spname['Name']);
			}

			$tDbQueryer->popQuery();

			$ResultString.="</td>\n<td class=\"border_right\">".$line['Standort']."</td>\n";
			$ResultString.=sprintf("<td>%s (%s)</td>\n",$line['bew_avg'],$line['bew_cnt']);
			$ResultString.="</tr>\n";
		}

		$tDbQueryer->popQuery();

		if(empty($ResultString))
		$ResultString="<tr><td colspan=\"4\">Keine passenden Einträge gefunden.</td></tr>";

		break;

	case "writecom":
		$fid="";
		$bew_sum=0;
		$bew_cnt=0;

		$text = (isset($_GET["text"]))?$_GET["text"]:"";

		try {
			$tDbQueryer->doQuery(sprintf("SELECT fid FROM Firmen WHERE Firmen.NAME='%s';",$_GET["comp"]));
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}

		while ($tDbQueryer->hasMoreResults()) {
			$line = $tDbQueryer->nextResultLine();
			$fid=$line['fid'];
		}

		try {
			$tDbQueryer->doQuery(sprintf("INSERT INTO Bewertungen (Bewertung, Kommentar, Gehoert_Zu_FID) VALUES(%s,'%s',%s);",$_GET["bw"],$text,$fid));
			$tDbQueryer->doQuery( "UPDATE Firmen SET bew_cnt=bew_cnt+1 WHERE fid=$fid");
			$tDbQueryer->doQuery("SELECT bew_cnt FROM Firmen WHERE fid=$fid");
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}

		while ($tDbQueryer->hasMoreResults()) {
			$line = $tDbQueryer->nextResultLine();
			$bew_cnt=floatval($line['bew_cnt']);
		}

		try {
			$tDbQueryer->doQuery("SELECT Bewertung FROM Bewertungen WHERE Gehoert_Zu_FID=$fid");
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}

		while ($tDbQueryer->hasMoreResults()) {
			$line = $tDbQueryer->nextResultLine();
			$bew_sum+=floatval($line['Bewertung']);
		}

		$bew_avg=$bew_sum/$bew_cnt;

		try {
			$tDbQueryer->doQuery(sprintf("UPDATE Firmen SET bew_avg='%s' WHERE fid=%d;",$bew_avg,$fid));
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}
		break;
			
	case "xmlexp":

		@date_default_timezone_set("GMT");
			
		header('Content-Type: application/xml,charset=UTF-8');
		header("Vary: Accept");
		header('Content-Disposition: attachment; filename="dbase.xml"');

		$writer = new XMLWriter();
		$writer->openURI('php://output');
		$writer->startDocument('1.0','UTF-8');
		$writer->setIndent(4);
		

		$writer->startElement('firmendatenbank');

		try {
			$tDbQueryer->doQuery("SELECT TID, Name FROM Themen");
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}

		$writer->startElement('themenliste');
				
		while ($tDbQueryer->hasMoreResults()) {
			$curRow = $tDbQueryer->nextResultLine();
			
			$writer->startElement("thema");
			$writer->writeAttribute("id", "t".$curRow['TID']);
			$writer->writeElement("name",$curRow['Name']);
			$writer->endElement();
		}

		$writer->endElement();
		
		try {
			$tDbQueryer->doQuery("SELECT SID, Name FROM Studienschwerpunkte");
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}
		
		$writer->startElement('schwerpunktliste');
		
		while ($tDbQueryer->hasMoreResults()) {
			$curRow = $tDbQueryer->nextResultLine();
				
			$writer->startElement("schwerpunkt");
			$writer->writeAttribute("id", "s".$curRow['SID']);
			$writer->writeElement("name",$curRow['Name']);
			$writer->endElement();
		}
		
		$writer->endElement();		
		
		try {
			$tDbQueryer->pushQuery("SELECT * FROM Firmen");
		} catch (Exception $e) {
			$ResultString.=$e->getMessage();
			break;
		}
		
		$writer->startElement('firmenliste');
		
		while ($tDbQueryer->hasMoreResults()) {
			$curRowI = $tDbQueryer->nextResultLine();
		
			$fid=$curRowI['FID'];
			
			$writer->startElement("firma");
			$writer->writeElement("name",$curRowI['Name']);
			$writer->startElement("adresse");
			$writer->writeElement("strasse",$curRowI['Straße']);
			$writer->writeElement("ort",$curRowI['Standort']);
			$writer->writeElement("plz",$curRowI['PLZ']);
			$writer->endElement();
			$writer->startElement("kontakt");
			$writer->writeElement("email",$curRowI['Email']);
			$writer->writeElement("url",$curRowI['URL']);
			$writer->endElement();
			
			$writer->startElement("bewertungen");
			$writer->writeElement("bew_cnt",$curRowI['bew_cnt']);
			$writer->writeElement("bew_avg",$curRowI['bew_avg']);
			
			try {
				$tDbQueryer->pushQuery("SELECT Bewertung, Kommentar FROM Bewertungen WHERE Gehoert_Zu_FID=$fid");
			} catch (Exception $e) {
				$ResultString.=$e->getMessage();
				break;
			}
						
			while ($tDbQueryer->hasMoreResults()) {
				$curRowJ = $tDbQueryer->nextResultLine();
					
				$writer->startElement("bewertung");
				$writer->writeElement("note",$curRowJ['Bewertung']);
				$writer->writeElement("kommentar",$curRowJ[	'Kommentar']);
				$writer->endElement();
			}

			$tDbQueryer->popQuery();
			$writer->endElement();
			
					
			try {
				$tDbQueryer->pushQuery("SELECT Th.TID FROM Themen Th, Behandelt_Thema BeTh WHERE BeTh.FID_FK=$fid AND Th.TID = BeTh.TID_FK");
			} catch (Exception $e) {
				$ResultString.=$e->getMessage();
				break;
			}

			$writer->startElement("themen");
			
			while ($tDbQueryer->hasMoreResults()) {
				$curRowJ = $tDbQueryer->nextResultLine();
				
				$writer->startElement("thema");
				$writer->writeAttribute("id", "t".$curRowJ['TID']);
				$writer->endElement();
			}
			
			$tDbQueryer->popQuery();			
			$writer->endElement();

			
			try {
				$tDbQueryer->pushQuery("SELECT Sp.SID FROM Studienschwerpunkte Sp, DecktAb_Schwerpunkt DASp WHERE DASp.FID_FK=$fid AND Sp.SID = DASp.SID_FK");
			} catch (Exception $e) {
				$ResultString.=$e->getMessage();
				break;
			}
			
			$writer->startElement("schwerpunkte");
				
			while ($tDbQueryer->hasMoreResults()) {
				$curRowJ = $tDbQueryer->nextResultLine();
			
				$writer->startElement("schwerpunkt");
				$writer->writeAttribute("id", "s".$curRowJ['SID']);
				$writer->endElement();
			}
				
			$tDbQueryer->popQuery();
			$writer->endElement();			
			
			$writer->endElement();

		}
		
		$writer->endElement();
		
		$writer->endDocument();
		$writer->flush();	
	default:
		break;
}

$tDbQueryer->closeConnection();

echo("$ResultString");
?>
